package com.example.util;

import java.sql.*;

public class BaseDao {
    //jdbc驱动
    static final String driver = "com.mysql.cj.jdbc.Driver";
    static final String url = "jdbc:mysql://localhost:3306/iotdata?serverTimezone=UTC";
    static final String user = "root";
    static final String password = "root";

    //获得数据库连接
    protected Connection getConnection() {
        try {
            Class.forName(driver);
            return DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //关闭所有连接
    public void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null && !resultSet.isClosed()) {
                resultSet.close();
            }
            if (statement != null && !statement.isClosed()){
                statement.close();
            }
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //执行sql语句（必须是insert update delete之一）
    protected void executeUpdate(String sql) {
        //得到连接
        Connection connection = getConnection();
        Statement statement = null;
        try {
            //创建一个执行对象
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            //执行sql语句
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //关闭所有
        closeAll(connection, statement, null);
    }

    //执行sql语句（查询语句）
    protected ResultSet executeSelect(String sql) {
        //得到连接
        Connection connection = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            //创建查询请求
            ps = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //返回查询结果
            rs = ps.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;
    }

//    public static void main(String[] args) {
//        try {
//            // 尝试建立数据库连接
//            Connection connection = new BaseDao().getConnection();
//            System.out.println("数据库连接成功！");
//
//            // 可以在这里执行其他数据库操作
//            Statement statement = connection.createStatement();
//            String address = "长沙市开福区";
//            String sql = "CREATE TABLE IF NOT EXISTS "+ address +" (" +
//                    "id INT AUTO_INCREMENT PRIMARY KEY," +
//                    "date VARCHAR ( 255 ) NOT NULL," +
//                    "time VARCHAR ( 255 ) NOT NULL," +
//                    "temperature VARCHAR ( 255 ) NOT NULL," +
//                    "humidity VARCHAR ( 255 ) NOT NULL," +
//                    "co2 VARCHAR ( 255 ) NOT NULL," +
//                    "lux VARCHAR ( 255 ) NOT NULL " +
//                    ");";
//            statement.execute(sql);
//
//            // 关闭连接
//            connection.close();
//        } catch (SQLException e) {
//            System.err.println("数据库连接失败！");
//            e.printStackTrace();
//        }
//    }
}
